package com.example.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.entity.Order;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

@Mapper
public interface OrderMapper extends BaseMapper<Order> {

//     sql 的开始时间 和 结束时间 可以写死 也可以 不写死 我这里就没写死
    @Select("select date(create_time) as date, count(*) as total, " +
            "sum(case when is_overdue = 1 then 1 else 0 end) as overdue, " +
            "sum(case when is_overdue = 1 then 1 else 0 end) / count(*) as overdue_rate " +
            "from orders where create_time between #{startDate} and #{endDate} " +
            "group by date(create_time)")
    List<Map<String, Object>> everyday(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);

    @Select("select d.dept_name, count(*) as total, " +
            "sum(case when o.is_overdue = 1 then 1 else 0 end) as overdue, " +
            "sum(case when o.is_overdue = 1 then 1 else 0 end) / count(*) as overdue_rate " +
            "from orders o left join department d on o.handle_dept_id = d.dept_id " +
            "where o.create_time between #{startDate} and #{endDate} " +
            "group by d.dept_name")
    List<Map<String, Object>> everydept(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);

    @Select("select order_type, count(*) as total, " +
            "sum(case when is_overdue = 1 then 1 else 0 end) as overdue, " +
            "sum(case when is_overdue = 1 then 1 else 0 end) / count(*) as overdue_rate " +
            "from orders where create_time between #{startDate} and #{endDate} " +
            "group by order_type")
    List<Map<String, Object>> everytype(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);

}

